package com.chencs.utils;

import java.io.*;
import java.net.URL;
import java.net.URLConnection;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.alibaba.fastjson.JSON;
import com.chencs.app.AppCommon;
import com.chencs.vo.biz.lianmeng.LmOrders;
import com.chencs.vo.biz.order.OrderModel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class XlsUtils {

    /**
     *
     * @param start_time
     * @param end_time
     * @param cookie
     * @param queryType 1
     */
    public static String downLoadXLS(String start_time,String end_time,String cookie,String queryType){
        String urlPath = "http://pub.alimama.com/report/getTbkPaymentDetails.json?queryType="+queryType+"&payStatus=&DownloadID=DOWNLOAD_REPORT_INCOME_NEW&startTime="+start_time+"&endTime="+end_time+"";
       // String cookie = "t=3d3818ef80d48c4da3447fa55d74c70f; account-path-guide-s1=true; 16510629_yxjh-filter-1=true; undefined_yxjh-filter-1=true; cna=wERrEvs2+G8CAXPGZHtq8mGq; excel-down-time-MKT_HOT_EXCEL_LIST=1510033187773; 129969440_yxjh-filter-1=true; qq-best-goods-down-time=1528335249498; cookie2=10f561669bdfac5006a1d467ec63b135; v=0; _tb_token_=313e3ed565e0e; alimamapwag=TW96aWxsYS81LjAgKFdpbmRvd3MgTlQgNi4xOyBXT1c2NCkgQXBwbGVXZWJLaXQvNTM3LjM2IChLSFRNTCwgbGlrZSBHZWNrbykgQ2hyb21lLzY3LjAuMzM5Ni43OSBTYWZhcmkvNTM3LjM2; cookie32=a3888a144f863985b889e5f4ed1edbc8; alimamapw=VhkJV1RbVgZWDTsGUAEGU1BUB1MDVFJaUVQCBFEFUlYBBwBUAQVVUAEGAQ%3D%3D; cookie31=MTY1MTA2MjksZnpoNjE4NTEyOSxmemg2MTg1MTI5QDEyNi5jb20sVEI%3D; login=URm48syIIVrSKA%3D%3D; rurl=aHR0cDovL3B1Yi5hbGltYW1hLmNvbS8%3D; apush6d83509f58cd1771638bcb989c91cf96=%7B%22ts%22%3A1528424254497%2C%22parentId%22%3A1528424249364%7D; isg=BOXl23710v20uDTdxxTvEXK99KHfipgrtBfkQefKkJwt_gRwrnIPhJLYjGKIfrFs";
        String filePatch = "d:/"+MyDateUtil.getStringAllDate()+".xls";
        try {
            URL url = new URL(urlPath);
            // 下载网络文件
            int bytesum = 0;
            int byteread = 0;
            URLConnection conn = url.openConnection();
            conn.setRequestProperty("Cookie", cookie);
            conn.setDoInput(true);
            InputStream inStream = conn.getInputStream();
            FileOutputStream fs = new FileOutputStream(filePatch);
            byte[] buffer = new byte[1204];
            int length;
            while ((byteread = inStream.read(buffer)) != -1) {
                bytesum += byteread;
                fs.write(buffer, 0, byteread);
            }
            fs.flush();
            System.out.println("读取成功:"+filePatch);
            return  filePatch;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("读取订单失败!");
        }
            return  "-1";
    }

    /**
     *
     * @param filePath
     * @return
     */
    public static Workbook readExcel(String filePath){
        Workbook wb = null;
        if(filePath==null){
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if(".xls".equals(extString)){
                return wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(extString)){
                return wb = new XSSFWorkbook(is);
            }else{
                return wb = null;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     *
     * @param cell
     * @return
     */
    public static Object getCellFormatValue(Cell cell){
        Object cellValue = null;
        if(cell!=null){
            //判断cell类型
            switch(cell.getCellType()){
                case Cell.CELL_TYPE_NUMERIC:{
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                }
                case Cell.CELL_TYPE_FORMULA:{
                    //判断cell是否为日期格式
                    if(DateUtil.isCellDateFormatted(cell)){
                        //转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    }else{
                        //数字
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING:{
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        }else{
            cellValue = "";
        }
        return cellValue;
        }


    /**
     * 解析订单
     * @param filePath
     * @param lev
     * @return
     */
    public static  List<LmOrders>  analysisXML(String filePath,Double lev){
        Workbook wb =null;
        Sheet sheet = null;
        Row row = null;
        List<LmOrders> list = null;
        String cellData = null;
       wb = readExcel(filePath);
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<LmOrders>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一行
            row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            for (int i = 1; i<rownum; i++) {

                LmOrders orderModel = new LmOrders();
                row = sheet.getRow(i);
                if(row !=null){
                    orderModel.setCreate_time( row.getCell(0).getStringCellValue());
                    orderModel.setClick_time( row.getCell(1).getStringCellValue());
                    orderModel.setPname( row.getCell(2).getStringCellValue());
                    orderModel.setPid( row.getCell(3).getStringCellValue());
                    orderModel.setShop_ww( row.getCell(4).getStringCellValue());
                    orderModel.setShop_name( row.getCell(5).getStringCellValue());
                    orderModel.setNums(row.getCell(6).getNumericCellValue()+"");
                    orderModel.setOld_price( row.getCell(7).getNumericCellValue());

                    orderModel.setOrder_flag( row.getCell(8).getStringCellValue());
                    orderModel.setOrder_state(getOrderState(orderModel.getOrder_flag()));
                    orderModel.setPay_price( row.getCell(12).getNumericCellValue());
                    orderModel.setProfit_price( row.getCell(13).getNumericCellValue());
                    orderModel.setRebate_price(Double.parseDouble(m2(row.getCell(13).getNumericCellValue()*lev)));
                    orderModel.setEstimate_price( row.getCell(15).getNumericCellValue());
                    orderModel.setEstimate_time( row.getCell(16).getStringCellValue());

//                    orderModel.setCreate_time( row.getCell(17).getStringCellValue());
//                    orderModel.setCreate_time( row.getCell(18).getStringCellValue());
//                    orderModel.setCreate_time( row.getCell(19).getStringCellValue());
//                    orderModel.setCreate_time( row.getCell(20).getStringCellValue());
//                    orderModel.setCreate_time( row.getCell(21).getStringCellValue());
//                    orderModel.setCreate_time( row.getCell(22).getStringCellValue());
//                    orderModel.setCreate_time( row.getCell(23).getStringCellValue());

                    orderModel.setOrder_no( row.getCell(25).getStringCellValue());
                    orderModel.setOrder_source( row.getCell(27).getStringCellValue());
                    orderModel.setAdvert_id( row.getCell(29).getStringCellValue());
                    orderModel.setAdvert_name( row.getCell(30).getStringCellValue());

                }else{
                    break;
                }
                list.add(orderModel);
            }
        }
//        //遍历解析出来的list
//        for (Map<String,String> map : list) {
//            for (Map.Entry<String,String> entry : map.entrySet()) {
//                System.out.print(entry.getKey()+":"+entry.getValue()+",");
//            }
//            System.out.println();
//        }
        return  list;
    }


    /**
     * 计算
     * @param orderFlag
     * @return
     */
    public  static String getOrderState(String orderFlag){

        if("订单失效".equals(orderFlag)){
            return -1+"";
        }else if("订单付款".equals(orderFlag)){
                return 0+"";
        }else {
            return 1+"";
        }

    }


    public static String m2(double data) {
        String datas=String.valueOf(data);
        String[] strArr= datas.split("[.]");
        if (strArr[1].length()<3) {
            return strArr[0]+"."+strArr[1];
        }

        if (strArr[1]==null||strArr[1].equals("0")){
            return strArr[0]+".00";
        }
        if (strArr[0]==null||strArr[0].equals("0")){
            return "0."+strArr[1].substring(0,2);
        }
        String doubleString=strArr[0]+"."+strArr[1].substring(0,2);
        return doubleString;
    }


    public static void main(String[] args) {
     String cookie = "t=3d3818ef80d48c4da3447fa55d74c70f; account-path-guide-s1=true; 16510629_yxjh-filter-1=true; undefined_yxjh-filter-1=true; cna=wERrEvs2+G8CAXPGZHtq8mGq; excel-down-time-MKT_HOT_EXCEL_LIST=1510033187773; 129969440_yxjh-filter-1=true; qq-best-goods-down-time=1528335249498; cookie2=13ef0aa7939386506ee8797a02e84038; v=0; _tb_token_=73033f348b3a5; alimamapwag=TW96aWxsYS81LjAgKFdpbmRvd3MgTlQgNi4xOyBXT1c2NCkgQXBwbGVXZWJLaXQvNTM3LjM2IChLSFRNTCwgbGlrZSBHZWNrbykgQ2hyb21lLzY3LjAuMzM5Ni45OSBTYWZhcmkvNTM3LjM2; cookie32=a3888a144f863985b889e5f4ed1edbc8; alimamapw=VUhYBVIPVARQATAEUVIEB1BVBAJSBlQOU1YECFoHUwUDUwBVAlQEAgdSAw%3D%3D; cookie31=MTY1MTA2MjksZnpoNjE4NTEyOSxmemg2MTg1MTI5QDEyNi5jb20sVEI%3D; login=WqG3DMC9VAQiUQ%3D%3D; pub-message-center=1; JSESSIONID=1226622D00C2252517B58F6CC20E061C; rurl=aHR0cDovL3B1Yi5hbGltYW1hLmNvbS8%3D; apush6d83509f58cd1771638bcb989c91cf96=%7B%22ts%22%3A1531139211698%2C%22parentId%22%3A1531139198572%7D; isg=BCkpCHJPVhujEWgJq6DrHVZRONVDXhy3nQ8Uu8sf15BukkukEkCg-NLEUHYBCrVg; sec=5b409ba58bbc15e21358f7a64802e992a487eaa2";
     String filePatch =   XlsUtils.downLoadXLS("2018-06-07", "2018-06-08", cookie, "1");
        List<LmOrders>  list =  XlsUtils.analysisXML(filePatch,0.6);
        System.out.println( JSON.toJSONString(list));
//        for (Map<String,String> map : list) {
//            for (Map.Entry<String,String> entry : map.entrySet()) {
//                System.out.print(entry.getKey()+":"+entry.getValue()+",");
//            }
//            System.out.println();
//        }



    }


}
